package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.FoodDao;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.po.Food;
import com.neusoft.elm.util.DBUtil;

public class FoodDaoImpl implements FoodDao {
    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    @Override
    public List<Food> listFoodByBusinessId(Integer businessId) throws Exception {
        List<Food> list = new ArrayList<>();
        String sql = "select * from food where businessId=? order by foodId";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, businessId);
            rs = pst.executeQuery();
            while (rs.next()) {
                Food food = new Food();
                food.setFoodId(rs.getInt("foodId"));
                food.setFoodName(rs.getString("foodName"));
                food.setFoodExplain(rs.getString("foodExplain"));
                food.setFoodImg(rs.getString("foodImg"));
                food.setFoodPrice(rs.getDouble("foodPrice"));
                food.setBusinessId(rs.getInt("businessId"));
                food.setRemarks(rs.getString("remarks"));
                list.add(food);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return list;
    }
    
    @Override
    public int saveFood(Food food) throws Exception {
        int result = 0;
        String sql = "INSERT INTO food (foodName, foodExplain, foodImg, foodPrice, businessId, remarks) " +
                     "VALUES (?, ?, ?, ?, ?, ?)";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, food.getFoodName());
            pst.setString(2, food.getFoodExplain());
            pst.setString(3, food.getFoodImg());
            pst.setDouble(4, food.getFoodPrice());
            pst.setInt(5, food.getBusinessId());
            pst.setString(6, food.getRemarks());
            result = pst.executeUpdate();
        } finally {
            DBUtil.close(pst);
        }
        return result;
    }
    
    @Override
    public int updateFood(Food food) throws Exception {
        int result = 0;
        String sql = "UPDATE food SET foodName=?, foodExplain=?, foodImg=?, " +
                     "foodPrice=?, businessId=?, remarks=? WHERE foodId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, food.getFoodName());
            pst.setString(2, food.getFoodExplain());
            pst.setString(3, food.getFoodImg());
            pst.setDouble(4, food.getFoodPrice());
            pst.setInt(5, food.getBusinessId());
            pst.setString(6, food.getRemarks());
            pst.setInt(7, food.getFoodId());
            result = pst.executeUpdate();
        } finally {
            DBUtil.close(pst);
        }
        return result;
    }
    
    @Override
    public int removeFood(Integer foodId) throws Exception {
        int result = 0;
        String sql = "DELETE FROM food WHERE foodId=?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, foodId);
            result = pst.executeUpdate();
        } finally {
            DBUtil.close(pst);
        }
        return result;
    }
    
    @Override
    public Food getFoodById(Integer foodId) throws Exception {
        Food food = null;
        String sql = "SELECT * FROM food WHERE foodId = ?";
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, foodId);
            rs = pst.executeQuery();
            if (rs.next()) {
                food = new Food();
                food.setFoodId(rs.getInt("foodId"));
                food.setFoodName(rs.getString("foodName"));
                food.setFoodExplain(rs.getString("foodExplain"));
                food.setFoodImg(rs.getString("foodImg"));
                food.setFoodPrice(rs.getDouble("foodPrice"));
                food.setBusinessId(rs.getInt("businessId"));
                food.setRemarks(rs.getString("remarks"));
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return food;
    }
    
 // 商家管理获取食品列表实现
    @Override
    public List<Food> listFoodForManage(Integer businessId, String searchKeyword, 
                                      String sortField, int page, int pageSize) 
                                      throws Exception {
        List<Food> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer("SELECT * FROM food WHERE businessId = ?");
        
        // 添加搜索条件
        if (searchKeyword != null && !searchKeyword.trim().isEmpty()) {
            sql.append(" AND foodName LIKE ?");
        }
        
        // 添加排序
        if ("foodPrice".equals(sortField)) {
            sql.append(" ORDER BY foodPrice ASC");
        } else if ("foodName".equals(sortField)) {
            sql.append(" ORDER BY foodName ASC");
        } else {
            sql.append(" ORDER BY foodId ASC");
        }
        
        // 添加分页
        sql.append(" LIMIT ? OFFSET ?");
        
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            
            int paramIndex = 1;
            pst.setInt(paramIndex++, businessId);
            
            if (searchKeyword != null && !searchKeyword.trim().isEmpty()) {
                pst.setString(paramIndex++, "%" + searchKeyword + "%");
            }
            
            pst.setInt(paramIndex++, pageSize);
            pst.setInt(paramIndex++, (page - 1) * pageSize);
            
            rs = pst.executeQuery();
            while (rs.next()) {
                Food food = new Food();
                food.setFoodId(rs.getInt("foodId"));
                food.setFoodName(rs.getString("foodName"));
                food.setFoodExplain(rs.getString("foodExplain"));
                food.setFoodImg(rs.getString("foodImg"));
                food.setFoodPrice(rs.getDouble("foodPrice"));
                food.setBusinessId(rs.getInt("businessId"));
                food.setRemarks(rs.getString("remarks"));
                list.add(food);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return list;
    }
    
    @Override
    public int countFoodByBusinessId(Integer businessId, String searchKeyword) throws Exception {
        int count = 0;
        StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM food WHERE businessId = ?");
        
        if (searchKeyword != null && !searchKeyword.trim().isEmpty()) {
            sql.append(" AND foodName LIKE ?");
        }
        
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            
            int paramIndex = 1;
            pst.setInt(paramIndex++, businessId);
            
            if (searchKeyword != null && !searchKeyword.trim().isEmpty()) {
                pst.setString(paramIndex++, "%" + searchKeyword + "%");
            }
            
            rs = pst.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return count;
    }
}